package dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import dao.NoticeDao;
import domain.Notice;
import domain.Order;
import domain.User;
import util.DataSourceUtils;

public class NoticeDaoImpl implements NoticeDao {

	@Override
	public List<Notice> findAllNotices(int page, int limit) throws SQLException {
		String sql = "select notice.* from notice limit ?,?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<List<Notice>> () {

			@Override
			public List<Notice> handle(ResultSet rs) throws SQLException {
				List<Notice> notice = new ArrayList<Notice>();
				
				while (rs.next()) {
					Notice item = new Notice();
					item.setId(rs.getInt("notice.n_id"));
					item.setTitle(rs.getString("notice.title"));
					item.setDetails(rs.getString("notice.details"));
					item.setN_time(rs.getDate("notice.n_time"));
					notice.add(item);
				}
				
				return notice;
			}
			
		}, (page - 1) * limit, limit);
	}

	@Override
	public boolean deleteNoticeById(String id) throws SQLException {
		String sql = "delete from notice where n_id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, id);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean addNotice(Notice notice) throws SQLException {
		String sql = "insert into notice(title,details,n_time) values(?,?,?)";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, notice.getTitle(), notice.getDetails(), notice.getN_time().toLocaleString());
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public Notice findNoticeById(String id) throws SQLException {
		String sql = "select notice.* from notice where n_id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<Notice> () {

			@Override
			public Notice handle(ResultSet rs) throws SQLException {
				Notice notice = new Notice();
				
				while (rs.next()) {
					notice.setId(rs.getInt("notice.n_id"));
					notice.setTitle(rs.getString("notice.title"));
					notice.setDetails(rs.getString("notice.details"));
					notice.setN_time(rs.getDate("notice.n_time"));
				}
				
				return notice;
			}
			
		}, id);
	}

	@Override
	public boolean update(Notice notice) throws SQLException {
		String sql = "update notice set title=?,details=? where n_id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, notice.getTitle(), notice.getDetails(), notice.getId());
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<Notice> findNoticesOrderByTime() throws SQLException {
		String sql = "select notice.* from notice order by n_time desc limit 0,5";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<List<Notice>> () {

			@Override
			public List<Notice> handle(ResultSet rs) throws SQLException {
				List<Notice> notice = new ArrayList<Notice>();
				
				while (rs.next()) {
					Notice item = new Notice();
					item.setId(rs.getInt("notice.n_id"));
					item.setTitle(rs.getString("notice.title"));
					item.setDetails(rs.getString("notice.details"));
					item.setN_time(rs.getDate("notice.n_time"));
					notice.add(item);
				}
				
				return notice;
			}
			
		});
	}

}
